/**  
 * 文 件 名: GroupsDAO.java  
 * 描    述：   
 * 版    权：Copyright (c)20012-2012 tongzhou All Rights Reserved.
 * 公    司: 同舟 
 * 作    者：彭定雄                     
 * 版    本: 1.0  
 * 创建时间: 2012-10-27
 *  
 * 修改历史：  
 * 时间                             作者                       版本                        描述  
 * ------------------------------------------------------------------  
 * 2012-10-27        彭定雄                1.0          1.0 Version  
 */        

/**
 * 
 */
package com.tongzhou.system.rightsMgmt.dao.impl;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.tongzhou.db.DBUtil;
import com.tongzhou.db.exception.DBException;
import com.tongzhou.system.rightsMgmt.dao.IGroupsDAO;
import com.tongzhou.system.rightsMgmt.model.Groups;
import com.tongzhou.system.rightsMgmt.model.Right;
import com.tongzhou.system.rightsMgmt.model.Role;
import com.tongzhou.system.rightsMgmt.model.SearchGroupsCondition;
import com.tongzhou.util.Util;

public class GroupsDAO implements IGroupsDAO{

	@Override
	public void addGroups(Groups groups) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("insert into sys_groups(name,active,create_by,create_time,update_by,update_time) ");
		sql.append("values('"+groups.getName()+"','"+groups.getActive()+"',");
		sql.append("'"+groups.getCreate_by()+"',now(),");
		sql.append("'"+groups.getCreate_by()+"',now())");
		DBUtil.update(sql.toString());
	}

	@Override
	public List<Groups> checkGroupsByName(Groups groups) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select name from sys_groups where name='"+groups.getName()+"' and id!='"+groups.getId()+"'");
		List<Groups> result=DBUtil.query(sql.toString(),new BeanListHandler<Groups>(Groups.class));
		return result;
	}

	@Override
	public void deleteGroups(Groups groups) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("delete from sys_groups where id='"+groups.getId()+"'");
		DBUtil.update(sql.toString());
		
		sql.delete(0, sql.length());
		sql.append("delete from sys_role_groups_relation where groups_id='"+groups.getId()+"'");
		DBUtil.update(sql.toString());
		
		sql.delete(0, sql.length());
		sql.append("delete from sys_groups_right_relation where groups_id='"+groups.getId()+"'");
		DBUtil.update(sql.toString());
	}

	@Override
	public List<Groups> getAllGroups() throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select g.id,g.name ");
		sql.append(" from sys_groups g ");
		sql.append(" where 1=1 ");
		List<Groups> result=DBUtil.query(sql.toString(),new BeanListHandler<Groups>(Groups.class));
		return result;
	}

	@Override
	public Groups getGroupsById(Groups groups) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select r.id,r.name,r.active,r.create_time,r.create_by,r.update_time,r.update_by,u1.name as creator,u2.name as updator ");
		sql.append(" from sys_groups r left join sys_user u1 on u1.id=r.create_by ");
		sql.append(" left join sys_user u2 on u2.id=r.update_by ");
		sql.append(" where 1=1 ");
		sql.append(" and r.id='"+groups.getId()+"'");
		Groups groups1=DBUtil.query(sql.toString(), new BeanHandler<Groups>(Groups.class));
		return groups1;
	}

	@Override
	public List<Groups> listGroups(SearchGroupsCondition condition)
			throws DBException {
		List<Groups> result=null;
		//查询条件SQL
		//查询参数
		StringBuffer sqlCondition = new StringBuffer() ;
		List<String> paramList = new ArrayList<String>() ;
		
		if(!Util.checkObjIsNull(condition.getName())){
			sqlCondition.append(" and r.name like ?") ;
			paramList.add("%"+condition.getName()+"%") ;
		}
		if(!Util.checkObjIsNull(condition.getActive())){
			sqlCondition.append(" and r.active=?");
			paramList.add(condition.getActive());
		}
		
		//取得满足条件的总记录数和总页数
		StringBuffer sql = new StringBuffer() ;
		sql.append("select count(*) from sys_groups r ");
		sql.append(" where 1=1 ");
		sql.append(sqlCondition);
		
		//总记录数
		long total = DBUtil.getCount(sql.toString(),paramList.toArray()) ;
		//总页数
		long totalPage = Util.getTotalPage(total, condition.getPageSize());
		
		condition.setTotal(total);
		condition.setTotalPage(totalPage);
		
		//查询某页的列表数据
		sql.delete(0, sql.length());
		sql.append("select r.id,r.name,r.active,r.create_time,r.create_by,r.update_time,r.update_by,u1.name as creator,u2.name as updator ");
		sql.append(" from sys_groups r left join sys_user u1 on u1.id=r.create_by ");
		sql.append(" left join sys_user u2 on u2.id=r.update_by ");
		sql.append(" where 1=1 ");
		sql.append(sqlCondition);
		sql.append(" order by "+condition.getOrderBy()+" "+condition.getOrderByType()) ;
		result = DBUtil.query(sql.toString(), new BeanListHandler<Groups>(Groups.class), paramList.toArray(), Util.getStartPos(condition.getCurrentPage(), condition.getPageSize()), condition.getPageSize()) ;
		return result;
	}

	@Override
	public void updateGroups(Groups groups) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("update sys_groups set ");
		sql.append("name='"+groups.getName()+"',active='"+groups.getActive()+"',");
		sql.append("update_by='"+groups.getUpdate_by()+"',update_time=now()");
		sql.append(" where id='"+groups.getId()+"' ");
		DBUtil.update(sql.toString());
	}

	@Override
	public List<Role> getRoleByGroupsId(Groups groups) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select u.role_id as id,g.name ");
		sql.append(" from sys_role_groups_relation u ");
		sql.append(" inner join sys_role g on g.id=u.role_id ");
		sql.append(" where 1=1 ");
		sql.append(" and u.groups_id='"+groups.getId()+"'");
		List<Role> result=DBUtil.query(sql.toString(),new BeanListHandler<Role>(Role.class));
		return result;
	}

	@Override
	public void updateGroupsRole(Groups groups) throws DBException {
		String[] strs=groups.getItemsVal().split("`");
		StringBuffer sql=new StringBuffer();
		sql.append("delete from sys_role_groups_relation where groups_id='"+groups.getId()+"'");
		DBUtil.update(sql.toString());
		if(!Util.checkObjIsNull(groups.getItemsVal())){
			for(int i=0;i<strs.length;i++){
				sql.delete(0, sql.length());
				sql.append("insert into sys_role_groups_relation(role_id,groups_id) ");
				sql.append(" values('"+strs[i]+"','"+groups.getId()+"'); ");
				DBUtil.update(sql.toString());
			}
		}
	}

	@Override
	public List<Right> getGroupsRightById(Groups groups) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select right_id as id from sys_groups_right_relation where groups_id='"+groups.getId()+"'");
		List<Right> result=DBUtil.query(sql.toString(),new BeanListHandler<Right>(Right.class));
		return result;
	}

	@Override
	public void updateGroupsRight(Groups groups) throws DBException {
		String[] rightIds=groups.getItemsVal().split("`");
		StringBuffer sql=new StringBuffer();
		sql.append("delete from sys_groups_right_relation where groups_id='"+groups.getId()+"'");
		DBUtil.update(sql.toString());
		if(!Util.checkObjIsNull(groups.getItemsVal())){
			for(int i=0;i<rightIds.length;i++){
				sql.delete(0, sql.length());
				sql.append("insert into sys_groups_right_relation(groups_id,right_id) ");
				sql.append(" values('"+groups.getId()+"','"+rightIds[i]+"'); ");
				DBUtil.update(sql.toString());
			}
		}
	}
}
